/*
 * hkh-project
 * leafteamwork.blogspot.com
 * Tool: Netbeans 6.7.1 - SQL Server 2005
 */
package vn.com.hkh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;
import vn.com.hkh.dto.ContactDTO;
import vn.com.hkh.util.DatabaseUtil;

/**
 *
 * @author leaf
 */
public class ContactDAO {

    // Log
    private static Logger logger = Logger.getLogger(PageDAO.class.getName());
    //C: Create
    // private final String SQL_CREATE = "INSERT INTO hkh_contact_table(contact_id, contact_fullname, contact_email, contact_address, contact_phone, contact_title, contact_content, contact_when) VALUES (?,?,?,?,?,?,?,?)";
    private final String SQL_CREATE = "INSERT INTO hkh_contact_table(contact_id, contact_fullname, contact_email, contact_address, contact_phone, contact_title, contact_content) VALUES (?,?,?,?,?,?,?)";
    //R: Read
    private final String SQL_READ_BY_ID = "SELECT contact_id, contact_fullname, contact_email, contact_address, contact_phone, contact_title, contact_content, CONVERT(varchar,contact_when,103) as contact_when FROM hkh_contact_table WHERE contact_id = ?";
    private final String SQL_READ_BY_ALL = "SELECT contact_id, contact_fullname, contact_email, contact_address, contact_phone, contact_title, contact_content, CONVERT(varchar,contact_when,103) as contact_when FROM hkh_contact_table ORDER BY contact_when DESC";
    //U: Update
    private final String SQL_UPDATE = "UPDATE hkh_contact_table SET contact_fullname=?, contact_email=?, contact_address=?, contact_phone=?, contact_title=?, contact_content=?, contact_when =? WHERE contact_id = ?";
    //D: Delete
    private final String SQL_DELETE_BY_ID = "DELETE FROM hkh_contact_table WHERE contact_id = ?";

    /**
     * Create new Contact
     * @param _contactDTO
     * @return
     */
    public ContactDTO create(ContactDTO _contactDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_CREATE);

            _ptmt.setString(1, _contactDTO.getId());
            _ptmt.setString(2, _contactDTO.getFullname());
            _ptmt.setString(3, _contactDTO.getEmail());
            _ptmt.setString(4, _contactDTO.getAddress());
            _ptmt.setString(5, _contactDTO.getPhone());
            _ptmt.setString(6, _contactDTO.getTitle());
            _ptmt.setString(7, _contactDTO.getContent());
            // _ptmt.setString(8, _contactDTO.getWhen());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _contactDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Contact by Id
     * @param _id
     * @return ContactDTO
     */
    public ContactDTO readByID(String _id) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        ContactDTO _contactDTO = null;
        try {

            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_ID);
            _ptmt.setString(1, _id);

            ResultSet _rs = _ptmt.executeQuery();
            if (_rs.next()) {
                _contactDTO = new ContactDTO();
                _contactDTO.setId(_rs.getString(1));
                _contactDTO.setFullname(_rs.getString(2));
                _contactDTO.setEmail(_rs.getString(3));
                _contactDTO.setAddress(_rs.getString(4));
                _contactDTO.setPhone(_rs.getString(5));
                _contactDTO.setTitle(_rs.getString(6));
                _contactDTO.setContent(_rs.getString(7));
                _contactDTO.setWhen(_rs.getString(8));
            }
            _rs.close();
            _ptmt.close();
            return _contactDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Contact by All
     * @return Collection<ContactDTO>
     */
    public Collection<ContactDTO> readByAll() {
        Collection<ContactDTO> _list = null;
        Connection _con = null;
        Statement _stmt = null;
        ContactDTO _contactDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _stmt = _con.createStatement();
            ResultSet _rs = _stmt.executeQuery(SQL_READ_BY_ALL);

            _list = new ArrayList<ContactDTO>();
            while (_rs.next()) {
                _contactDTO = new ContactDTO();
                _contactDTO.setId(_rs.getString(1));
                _contactDTO.setFullname(_rs.getString(2));
                _contactDTO.setEmail(_rs.getString(3));
                _contactDTO.setAddress(_rs.getString(4));
                _contactDTO.setPhone(_rs.getString(5));
                _contactDTO.setTitle(_rs.getString(6));
                _contactDTO.setContent(_rs.getString(7));
                _contactDTO.setWhen(_rs.getString(8));

                _list.add(_contactDTO);
            }
            _rs.close();
            _stmt.close();
            return _list;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Update Contact
     * @param _contactDTO
     * @return ContactDTO
     */
    public ContactDTO update(ContactDTO _contactDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_UPDATE);

            _ptmt.setString(1, _contactDTO.getFullname());
            _ptmt.setString(2, _contactDTO.getEmail());
            _ptmt.setString(3, _contactDTO.getAddress());
            _ptmt.setString(4, _contactDTO.getPhone());
            _ptmt.setString(5, _contactDTO.getTitle());
            _ptmt.setString(6, _contactDTO.getContent());
            _ptmt.setString(7, _contactDTO.getWhen());
            _ptmt.setString(8, _contactDTO.getId());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _contactDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

     /**
     * Delete Contact
     * @param _contactID
     * @return boolean
     */
    public boolean deleteById(String _contactID) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_DELETE_BY_ID);
            _ptmt.setString(1, _contactID);
            _ptmt.executeUpdate();

            _ptmt.close();
            return true;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }
}
